package com.mmy.dao.line;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.line.TLineOrder;
import com.mmy.domain.line.VLineOrder;

public interface TLineOrderRepository extends JpaRepository<TLineOrder, Integer> {
	/**
	 * 根据订单Id查询
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select * from tLineOrder where iID = :iID", nativeQuery = true)
	TLineOrder findByID(@Param("iID") int iID);

	/**
	 * 获取最大流水号
	 * 
	 * @return 返回值说明 流水号
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "SELECT MAX(iID)  FROM tLineOrder ", nativeQuery = true)
	Object getMaxID();

	/**
	 * 获得订单最大编码
	 *
	 * @author 创建人dingxd
	 * @date 修改日期 2020.11.14
	 */
	@Query(value = "SELECT MAX(cOrderID) FROM tLineOrder WHERE LEFT(cOrderID,16) = CONCAT(:iPlanID,REPLACE(DATE(NOW()),'-',''))", nativeQuery = true)
	Object getMaxCode(@Param("iPlanID") String strPlanID);

	/*
	 * 新增id
	 */

	@Transactional
	@Modifying
	@Query(value = "INSERT INTO tLineOrder(iID, cOrderID, iReserveUserID, dReserveDate, cReserveName, cReserveTel, iOrderUserID, dOrderDate, cOrderName, cOrderTel, iOrderState, fOrderAmount, iOrderNumber, dLineDate, fReceivable, fReceipt, fFloor, fPeer, fPrice, iPayType, cVoucher, iCashierState, dCashierDate, iCashierID, iAccountState, dAccountDate, iAccountID, iInvoiceState, iRefundState, iRefundProcess, bRemittanceState, dCreateDate, cCreator, dModifyDate, cReviser, cMemo, cSpare1, cSpare2, cSpare3, cSpare4, cSpare5) VALUES (:id, NULL, 0, NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, NULL, 0, 0, NULL, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)", nativeQuery = true)
	void save(@Param("id") int id);

	/*
	 * 根据id删除
	 */
	@Transactional
	@Modifying
	@Query(value = "delete from tLineOrder where tLineOrder.iID in (:lstID)", nativeQuery = true)
	void delLineOrder(@Param("lstID") List<Integer> lstID);

	/*
	 * 根据出纳审核状态查找
	 */
	@Query(value = "select *  from tLineOrder where iCashierState = :strCashierState and left (cOrderID,9) = :iLineID", nativeQuery = true)
	List<TLineOrder> findByCashierAndLineID(@Param("strCashierState") String strCashierState,
			@Param("iLineID") int iLineID);

	/*
	 * 根据会计审核状态查找
	 */
	@Query(value = "select *  from tLineOrder where iAccountState = :strAccountState and left (cOrderID,9) = :iLineID", nativeQuery = true)
	List<TLineOrder> findByAccountAndLineID(@Param("strAccountState") String strAccountState,
			@Param("iLineID") int iLineID);

	/**
	 * 个人中心通过登录人获取线路预留
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tLineOrder where iReserveUserID=?1 and iOrderState=0 order by dReserveDate desc limit ?2,?3", nativeQuery = true)
	List<TLineOrder> findByUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取线路订单
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tLineOrder where iOrderUserID=?1 and iOrderState in(1,2) order by dOrderDate desc limit ?2,?3", nativeQuery = true)
	List<TLineOrder> findByOrderUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取线路退单
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tLineOrder where iOrderUserID=?1 and iOrderState =4 order by dOrderDate desc limit ?2,?3", nativeQuery = true)
	List<TLineOrder> findByChargeBackUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取线路预留 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from tLineOrder where iReserveUserID=?1 and iOrderState=0 order by dReserveDate desc", nativeQuery = true)
	int countByUserID(int iUserID);

	/**
	 * 个人中心通过登录人获取线路退单 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from tLineOrder where iOrderUserID=?1 and iOrderState =4 order by dOrderDate desc", nativeQuery = true)
	int countByRefundUserID(int iUserID);

	/**
	 * 个人中心通过登录人获取线路订单 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from tLineOrder where iOrderUserID=?1 and iOrderState in(1,2) order by dOrderDate desc", nativeQuery = true)
	int countByOrderUserID(int iUserID);

}
